package nl.saxion.dbi.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import nl.saxion.dbi.model.Database;
import nl.saxion.dbi.model.Key;
import nl.saxion.dbi.model.KeyPair;
import nl.saxion.dbi.model.KeyVeld;
import nl.saxion.dbi.model.Row;
import nl.saxion.dbi.model.Tabel;
import nl.saxion.dbi.model.Veld;

public class BazenbaseDao
{

	private static BazenbaseDao instance = new BazenbaseDao();

	private BazenbaseDao()
	{
	}

	public static BazenbaseDao getInstance()
	{
		return instance;
	}

	private DatabaseManager dbManager = DatabaseManager.getInstance();

	public List<Database> getDatabases()
	{
		try
		{
			List<Database> databases = new ArrayList<>();
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement("SELECT * FROM databases");
			ResultSet resultSet = prepareStatement.executeQuery();

			while (resultSet.next())
			{
				Database database = new Database();
				database.setId(resultSet.getInt("id"));
				database.setNaam(resultSet.getString("naam"));
				database.setTabellen(getTabellen(database.getId()));
				database.setKeyPairs(getKeyPairs(database.getTabellen()));
				databases.add(database);
			}
			return databases;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return null;
		}
	}

	public List<Tabel> getTabellen(int databaseId) throws SQLException
	{
		List<Tabel> tabellen = new ArrayList<>();

		PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
			"SELECT * FROM tabellen WHERE database_id = ?");
		prepareStatement.setInt(1, databaseId);
		ResultSet resultSet = prepareStatement.executeQuery();

		while (resultSet.next())
		{
			Tabel tabel = new Tabel();
			tabel.setId(resultSet.getInt("id"));
			tabel.setNaam(resultSet.getString("naam"));
			tabel.setVelden(getVelden(tabel.getId()));
			tabel.setPrimaryKey(getPrimaryKey(tabel.getId(), tabel.getVelden()));
			tabel.setKeys(getKeys(tabel.getVelden()));
			tabel.setData(new ArrayList<Row>());
			tabellen.add(tabel);
		}
		return tabellen;
	}

	public Key getPrimaryKey(int tabelId, List<Veld> velden) throws SQLException
	{

		PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
			"SELECT * FROM Keys JOIN primary_keys ON id = key_id WHERE tabel_id = ?");
		prepareStatement.setInt(1, tabelId);
		ResultSet resultSet = prepareStatement.executeQuery();

		if (resultSet.next())
		{
			Key key = new Key();
			key.setId(resultSet.getInt("id"));
			key.setNaam(resultSet.getString("naam"));
			key.setUnique(resultSet.getBoolean("is_unique"));
			key.setVelden(getKeyVelden(key, velden));
			return key;
		}
		return null;
	}

	public List<Veld> getVelden(int tabelId) throws SQLException
	{
		List<Veld> velden = new ArrayList<>();

		PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
			"SELECT * FROM velden WHERE tabel_id = ?");
		prepareStatement.setInt(1, tabelId);
		ResultSet resultSet = prepareStatement.executeQuery();

		while (resultSet.next())
		{
			Veld veld = new Veld();
			veld.setId(resultSet.getInt("id"));
			veld.setNaam(resultSet.getString("naam"));
			veld.setType(resultSet.getString("type"));
			veld.setNotNull(resultSet.getBoolean("not_null"));
			velden.add(veld);
		}
		return velden;
	}

	public List<Key> getKeys(List<Veld> velden) throws SQLException
	{
		List<Key> keys = new ArrayList<>();

		PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement("SELECT * FROM keys");
		ResultSet resultSet = prepareStatement.executeQuery();

		while (resultSet.next())
		{
			Key key = new Key();
			key.setId(resultSet.getInt("id"));
			key.setNaam(resultSet.getString("naam"));
			key.setUnique(resultSet.getBoolean("is_unique"));
			key.setVelden(getKeyVelden(key, velden));
			if (key.getVelden() != null && !key.getVelden().isEmpty())
			{
				keys.add(key);
			}
		}

		return keys;
	}

	private List<KeyVeld> getKeyVelden(Key key, List<Veld> velden) throws SQLException
	{
		List<KeyVeld> keyVelden = new ArrayList<>();

		PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
			"SELECT * from key_velden WHERE key_id = ?");
		prepareStatement.setInt(1, key.getId());
		ResultSet resultSet = prepareStatement.executeQuery();

		while (resultSet.next())
		{
			KeyVeld keyVeld = new KeyVeld();
			keyVeld.setKey(key);
			for (Veld veld : velden)
			{
				if (veld.getId() == resultSet.getInt("veld_id"))
				{
					keyVeld.setVeld(veld);
				}
			}
			keyVeld.setVolgnummer(resultSet.getInt("volgnummer"));
			if (keyVeld.getVeld() != null)
			{
				keyVelden.add(keyVeld);
			}
		}
		return keyVelden;
	}

	public List<KeyPair> getKeyPairs(List<Tabel> tabellen) throws SQLException
	{

		List<KeyPair> keyPairs = new ArrayList<KeyPair>();
		PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement("SELECT * FROM keys_keys");

		ResultSet resultSet = prepareStatement.executeQuery();

		while (resultSet.next())
		{
			KeyPair keyPair = new KeyPair();
			int fromKey = resultSet.getInt("from_key");
			int toKey = resultSet.getInt("to_key");
			for (Tabel tabel : tabellen)
			{
				for (Key key : tabel.getKeys())
				{
					if (key.getId() == fromKey)
					{
						keyPair.setFromKey(key);
					}
					if (key.getId() == toKey)
					{
						keyPair.setToKey(key);
					}
				}
			}

			if (keyPair.getFromKey() != null && keyPair.getToKey() != null)
			{
				keyPairs.add(keyPair);
			}
			else if (!(keyPair.getFromKey() == null && keyPair.getToKey() == null))
			{
				throw new IllegalStateException("Eenzijdige verwijzing gevonden met keys " + fromKey + " & " + toKey);
			}
		}
		return keyPairs;
	}

	public Database insertDatabase(String naam)
	{

		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
				"INSERT INTO databases (naam) VALUES (?) ", Statement.RETURN_GENERATED_KEYS);
			prepareStatement.setString(1, naam);

			prepareStatement.executeUpdate();
			ResultSet resultSet = prepareStatement.getGeneratedKeys();

			if (resultSet.next())
			{
				Database database = new Database();
				database.setId(resultSet.getInt(1));
				database.setNaam(naam);
				database.setTabellen(new ArrayList<Tabel>());
				database.setKeyPairs(new ArrayList<KeyPair>());
				return database;
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}

	public void deleteDatabase(String naam)
	{
		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
				"DELETE FROM databases WHERE naam = ?");
			prepareStatement.setString(1, naam);
			prepareStatement.execute();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public Tabel insertTabel(Database database, String naam)
	{
		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
				"INSERT INTO tabellen (naam, database_id) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS);
			prepareStatement.setString(1, naam);
			prepareStatement.setInt(2, database.getId());

			prepareStatement.executeUpdate();
			ResultSet resultSet = prepareStatement.getGeneratedKeys();

			if (resultSet.next())
			{
				Tabel tabel = new Tabel();
				tabel.setId(resultSet.getInt(1));
				tabel.setKeys(new ArrayList<Key>());
				tabel.setNaam(naam);
				tabel.setPrimaryKey(null); // Lege tabel heeft geen PK
				tabel.setVelden(new ArrayList<Veld>());
				tabel.setData(new ArrayList<Row>());
				return tabel;
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}

	public void deleteKeyPair(int fromKeyId)
	{
		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
				"DELETE FROM keys_keys WHERE from_key = ?");
			prepareStatement.setInt(1, fromKeyId);
			prepareStatement.executeUpdate();

			prepareStatement = dbManager.getConnection().prepareStatement("DELETE FROM keys_keys WHERE to_key = ?");
			prepareStatement.setInt(1, fromKeyId);
			prepareStatement.executeUpdate();

		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public void deleteKeyVelden(int keyId)
	{
		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
				"DELETE FROM key_velden WHERE key_id = ?");
			prepareStatement.setInt(1, keyId);
			prepareStatement.executeUpdate();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public void deletePrimaryKey(int keyId)
	{
		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
				"DELETE FROM primary_keys WHERE key_id = ?");
			prepareStatement.setInt(1, keyId);
			prepareStatement.executeUpdate();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public void deleteKey(int keyId)
	{
		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
				"DELETE FROM keys WHERE id = ?");
			prepareStatement.setInt(1, keyId);
			prepareStatement.executeUpdate();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public Veld addVeld(Tabel tabel, String naam, String type, boolean notNull)
	{
		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection()
				.prepareStatement("INSERT INTO velden (tabel_id, naam, type, not_null) VALUES (?,?,?,?)",
					Statement.RETURN_GENERATED_KEYS);
			prepareStatement.setInt(1, tabel.getId());
			prepareStatement.setString(2, naam);
			prepareStatement.setString(3, type);
			prepareStatement.setBoolean(4, notNull);
			prepareStatement.executeUpdate();
			ResultSet resultSet = prepareStatement.getGeneratedKeys();

			if (resultSet.next())
			{
				Veld veld = new Veld();
				veld.setId(resultSet.getInt(1));
				veld.setNaam(naam);
				veld.setType(type);
				veld.setNotNull(notNull);
				return veld;
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}

	public Key addKey(String naam, boolean unique)
	{
		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
				"INSERT INTO keys (naam, is_unique) VALUES(?,?)", Statement.RETURN_GENERATED_KEYS);
			prepareStatement.setString(1, naam);
			prepareStatement.setBoolean(2, unique);
			prepareStatement.executeUpdate();
			ResultSet resultSet = prepareStatement.getGeneratedKeys();

			if (resultSet.next())
			{
				Key key = new Key();
				key.setId(resultSet.getInt(1));
				key.setNaam(naam);
				key.setUnique(unique);
				key.setVelden(new ArrayList<KeyVeld>());
				return key;
			}

		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;

	}

	public KeyVeld addKeyVeld(Veld veld, Key key, int volgnummer)
	{
		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
				"INSERT INTO key_velden (veld_id, key_id, volgnummer) VALUES (?,?,?)");
			prepareStatement.setInt(1, veld.getId());
			prepareStatement.setInt(2, key.getId());
			prepareStatement.setInt(3, volgnummer);
			prepareStatement.executeUpdate();

			KeyVeld keyVeld = new KeyVeld();
			keyVeld.setVeld(veld);
			keyVeld.setKey(key);
			keyVeld.setVolgnummer(volgnummer);
			return keyVeld;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}

	public void setPrimaryKey(Tabel tabel, Key key)
	{
		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
				"INSERT INTO primary_keys (key_id, tabel_id) VALUES (?,?)");
			prepareStatement.setInt(1, key.getId());
			prepareStatement.setInt(2, tabel.getId());
			prepareStatement.executeUpdate();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public KeyPair insertKeyPair(Key fromKey, Key toKey)
	{
		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
				"INSERT INTO keys_keys (from_key, to_key) VALUES (?,?)");
			prepareStatement.setInt(1, fromKey.getId());
			prepareStatement.setInt(2, toKey.getId());
			prepareStatement.executeUpdate();
			KeyPair keyPair = new KeyPair();
			keyPair.setFromKey(fromKey);
			keyPair.setToKey(toKey);
			return keyPair;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}

	public void deleteVeld(Veld veld)
	{
		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
				"DELETE FROM velden WHERE id=?");
			prepareStatement.setInt(1, veld.getId());
			prepareStatement.execute();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public void deleteTabel(Tabel tabel)
	{
		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
				"DELETE FROM tabellen WHERE id=?");
			prepareStatement.setInt(1, tabel.getId());
			prepareStatement.execute();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public void deleteDatabase(Database database)
	{
		try
		{
			PreparedStatement prepareStatement = dbManager.getConnection().prepareStatement(
				"DELETE FROM databases WHERE id=?");
			prepareStatement.setInt(1, database.getId());
			prepareStatement.execute();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
}
